import wx   #wxpython
import wx.xrc
import sqlite3
from pubsub import pub #pypubsub
from hash_gui import *
from inc import *
from MyDataTable import MyDataTable
from hash_gui import *
from hashThread import *
import zipfile
import shutil
import os
########################################################################
class f2(MyFrame2):
    def __init__(self, parent,path):
        super().__init__(parent)
        self.root=path
        self.db=sqlite3.connect(f"{self.root}\\{logfilename}.db",check_same_thread=False)
    def MyFrame2OnClose(self, event):
        self.Destroy()
        event.Skip()
    def inputFile(self, event):
        event.Skip()
    def reFile( self, event ):
        dlg = wx.DirDialog(self, "备份文件夹",style=wx.DD_DEFAULT_STYLE)
        dlg.ShowModal()
        self.Destroy()
        w2=f2(None,self.root)
        p3(w2,self.root,dlg.GetPath())
        w2.Title=f"文件恢复 {self.root}"
        w2.Show()
        dlg.Destroy()
        event.Skip()
    def outputFile(self, event):
        cur=self.db.cursor()
        result = cur.execute("select count(*) from hash ")
        row = result.fetchone()
        total = row[0]
        BackupFileSize=int(getConfig(self.db,'BackupFileSize'))
        lens=int(total/BackupFileSize)+1
        dlg = wx.ProgressDialog("导出校验记录", " ", maximum = total,parent=None,style = 0| wx.PD_APP_MODAL | wx.PD_ESTIMATED_TIME| wx.PD_REMAINING_TIME )
        dlgn=0
        compression= int(getConfig(self.db,'compression')) 
        if compression not in [0,8,14,12]:
            compression=0
        with tqdm(total=total) as bar:
            #compression= (ZIP_STORED: 0 ,ZIP_DEFLATED:8,ZIP_BZIP2:12, ZIP_LZMA:14)
            with zipfile.ZipFile(f"{self.root}\\{logfilename[:-4]}bak.zip", 'w',compression=compression,allowZip64=True) as myzip:  #compression=zipfile.ZIP_LZMA,
                myzip.writestr(f"config.txt",f"{row[0]}")
                for i in range(lens):
                    sql_str=""
                    result = cur.execute(f"select * from hash LIMIT {BackupFileSize*i}, {BackupFileSize}") 
                    row=result.fetchall()
                    for n in row:
                        bar.update(1)
                        dlg.Update(dlgn,f"{dlgn}/{total}")
                        dlgn=dlgn+1
                        try:
                            text1=eccBase64En(n)
                            sql_str=sql_str+f"{n[0]}\t{text1}\n"
                        except:
                            logging.error(n[0])
                    myzip.writestr(f"{i}.log",sql_str)
        dlg.Destroy()
        msg("已成功导出hash")
        event.Skip()

    def onReset(self, event):
        if(msg(u"确定清空所有校验记录？", u"重置校验", wx.YES_NO | wx.ICON_INFORMATION) == wx.ID_YES):
            if(trySql(self.db,'DELETE FROM checks')):
                msg(u"已清空所有校验记录", u"成功")
            else:
                msg(u"清除失败", u"错误", wx.ICON_ERROR)

    def refreshList(self, event):
        if(msg(u"确定重新扫描目录？", u"更新目录", wx.YES_NO | wx.ICON_INFORMATION) == wx.ID_YES):
            
            trySql(self.db,'DELETE FROM list')
            trySql(self.db,'DELETE FROM dirs')
            ScanThread(self.root)
            event.Skip()

    def updateFileHash(self, event):
        if(msg(u"确定更新校验记录？", u"更新记录", wx.YES_NO | wx.ICON_INFORMATION) == wx.ID_YES):
            if(trySql(self.db,'update hash set hash=(select checks from `check` where path = hash.path) where path in (select path from `check`)')):
                msg(u"更新成功！")
            event.Skip()

    def delFilelist(self, event):
        if(msg(u"确定删除缺失记录？", u"删除文件", wx.YES_NO | wx.ICON_INFORMATION) == wx.ID_YES):
            if(trySql(self.db,'DELETE FROM `checks` WHERE path in (select path from `lostfile`)') and
                    trySql(self.db,'DELETE FROM `hash` WHERE path in (select path from `lostfile`)')):
                msg(u"删除成功！")
            event.Skip()

    def delCheckList(self,event):
        if(msg(u"确定删除差异文件校验记录？", u"重置校验", wx.YES_NO | wx.ICON_INFORMATION) == wx.ID_YES):
            if(trySql(self.db,'DELETE FROM `checks` WHERE path in (select path from `check`)')):
                msg(u"清除成功！")
        event.Skip()
########################################################################
class p2(MyPanel2):
    def __init__(self, parent,path):
        super().__init__(parent)
        self.root=path
        self.w2=parent
        self.table = "alllist"
        self.db = sqlite3.connect(f"{path}\\{logfilename}.db",check_same_thread=False)
        self.cur = self.db.cursor()
        self.getPageCount()
        self.cur=self.db.cursor()
        self.cur.row_factory=self.row_factory1
        self.showpage(1)
    def __del__(self):
        logging.info("exit p2")
    def onUpPage(self, event):
        page = self.m_comboBox3.GetStringSelection()
        self.showpage(int(page)-1)
        event.Skip()

    def OnChosePage(self, event):
        self.showpage(int(self.m_comboBox3.GetValue()))
        event.Skip()

    def OnGotoPage(self, event):
        if(self.m_comboBox3.GetValue().isdigit()):
            if(int(self.m_comboBox3.GetValue()) < 1 or int(self.m_comboBox3.GetValue()) > self.pageCount):
                dlg = wx.MessageDialog(None, u"超出页码范围", '', wx.ICON_WARNING)
                dlg.ShowModal()
                dlg.Destroy()
                self.m_comboBox3.SetSelection(0)
                return False
        else:
            dlg = wx.MessageDialog(None, u"请输入有效数字", '', wx.ICON_WARNING)
            dlg.ShowModal()
            dlg.Destroy()
            self.m_comboBox3.SetSelection(0)
            return False
        self.showpage(int(self.m_comboBox3.GetValue()))
        event.Skip()

    def OnDownPage(self, event):
        page = self.m_comboBox3.GetValue()
        self.showpage(int(page)+1)
        event.Skip()

    def OnShowAll(self, event):
        self.w2.SetTitle(u"查看记录")
        self.table = "alllist"
        self.getPageCount()
        self.cur=self.db.cursor()
        self.cur.row_factory=self.row_factory1
        self.showpage(1)
  
        event.Skip()

    def OnShowAddfile(self, event):
        self.w2.SetTitle(u"新增文件")
        self.table = "newfile"
        self.getPageCount()
        self.cur=self.db.cursor()
        self.showpage(1)
        event.Skip()

    def OnShowLostfile(self, event):
        self.w2.SetTitle(u"丢失文件")
        self.table = "lostfile"
        self.getPageCount()
        self.cur=self.db.cursor()
        self.showpage(1)
        event.Skip()

    def onReset(self, event):
        dlg = wx.MessageDialog(None, u"确定清空所有校验记录？",
                               u"重置校验", wx.YES_NO | wx.ICON_QUESTION)
        if dlg.ShowModal() == wx.ID_YES:
            try:
                self.db.execute('DELETE FROM checks')
            except (sqlite3.Error, Exception) as e:
                logging.error("Database error: %s" % e)
                self.db.rollback()
            else:
                self.db.commit()
        dlg.Destroy()

    def OnShowRepeat(self, event):
        self.w2.SetTitle(u"重复文件")
        self.table = "repeat"
        self.getPageCount()
        self.cur=self.db.cursor()
        self.showpage(1)
        event.Skip()

    def m_button6OnButtonClick(self, event):
        self.w2.SetTitle(u"差异文件")
        self.table = "check"
        self.getPageCount()
        self.cur=self.db.cursor()
        self.cur.row_factory=self.row_factory1 
        self.showpage(1)
        event.Skip()

    def showpage(self, index):
        self.w2.m_statusBar2.SetStatusText(u"正在加载")
        if(self.fileCount == 0):
            # print(msg("没有记录"))
            #self.table = "alllist"
            gridDatas = [(u"无记录", u"无记录", u"无记录", u"无记录")]
        else:
            sql = "select * from `%s` LIMIT %d, 1000" % (
                self.table, (index-1)*1000)
            logging.debug(sql)

            result = self.cur.execute(sql)
            logging.debug(sql)
            gridDatas = result.fetchall()
        # self.gridTable = wx.grid.Grid(self.panel, -1, pos=(5, 5), size=(490, 300), style=wx.WANTS_CHARS)
        self.infoTable = MyDataTable(gridDatas, [u"路径", u"校验记录", u"生成校验",u"时间"])
        self.m_grid1.SetTable(self.infoTable, True)
        self.m_grid1.SetColSize(0, 500)
        self.m_grid1.SetColSize(1, 280)
        # try:
        #     self.m_grid1.SetColSize(2, 280)
        # except:
        #     pass
        self.m_grid1.SetRowLabelSize(50)
        # self.m_grid1.AutoSize()
        self.m_grid1.Refresh()
        # self.m_comboBox3.SetValue(str(index))
        self.m_comboBox3.SetSelection(index-1)
        if(self.pageCount == 1):
            self.m_button11.Disable()
            self.m_button12.Disable()
        else:
            if(index == 1):
                self.m_button11.Disable()
                self.m_button12.Enable()
            if(index >= self.pageCount):
                self.m_button12.Disable()
                self.m_button11.Enable()
            if(index > 1 and index < self.pageCount):
                self.m_button11.Enable()
                self.m_button12.Enable()
        self.w2.m_statusBar2.SetStatusText(u"文件总计：%d" % (self.fileCount))
    def onExit(self, event):
        self.db.close()
        self.w2.Destroy()
        event.Skip()

    def getPageCount(self):
        cur=self.db.cursor()
        r = cur.execute("select count(*) from `%s`" % (self.table))
        rows = r.fetchall()
        self.fileCount = rows[0][0]
        self.pageCount = int(self.fileCount/1000)+1
        self.m_comboBox3.Clear()
        for i in range(self.pageCount):
            self.m_comboBox3.Append(str(i+1))
        self.m_comboBox3.SetSelection(0)
        cur=None
    def OnLDC(self, event):
        os.startfile(os.path.join(self.root, self.infoTable.data[self.m_grid1.GridCursorRow][0].lstrip("\\")))
        event.Skip()
    def row_factory1(self,cursor,row):
        try:
            if(row[2]==row[1]):
                r2=u"相符"
            else:
                r2=row[2]
            if(row[3]==None):
                r3=None
            else:
                r3=f"{round((time.time()-row[3])/86400,4):.4f}"
            return [row[0],row[1],r2,r3]
        except:
            logging.error(row[0])
########################################################################
class p3(MyPanel7):
    def __init__(self, parent,localPath,remotePath):
        super().__init__(parent)  
        self.w2=parent
        self.localPath=localPath
        self.remotePath=remotePath
        self.localDb=sqlite3.connect(localPath+"/"+logfilename+".db", check_same_thread=False)
        self.size=100
        self.cur=self.localDb.cursor()
        self.curf1=self.localDb.cursor()
        self.curf1.row_factory=self.rowfactory1
        pub.subscribe(self.uptextCtrl2,"uptextCtrl1")
        self.w2.SetTitle(f"{self.localPath}")
        self.copyRemoteDb()
        self.showlist(0)

    def m_grid4OnGridCellLeftClick(self,event):
        event.Skip()

    def m_grid4OnGridCellLeftDClick(self,event):
        logging.info("m_grid4OnGridCellLeftDClick")
        row=self.m_grid4.GetGridCursorRow()
        col=self.m_grid4.GetGridCursorCol()
        try:
            if(col==0):
                os.startfile(os.path.join(self.localPath, self.infoTable.data[self.m_grid4.GridCursorRow][0].lstrip("\\")))
            elif(col==3):
                os.startfile(os.path.join(self.remotePath, self.infoTable.data[self.m_grid4.GridCursorRow][0].lstrip("\\")))
        except :
            msg("文件无法打开")
        logging.info(f"row:{row},col:{col}")
        event.Skip()

    def m_grid4OnGridSelectCell(self,event):
        logging.info("m_grid4OnGridSelectCell")
        event.Skip()
    def m_grid4OnGridRangeSelect(self,event):
        lt=self.m_grid4.GetSelectionBlockTopLeft()
        rb=self.m_grid4.GetSelectionBlockBottomRight()
        logging.info(f"{lt}\t{rb}")
        event.Skip()

    def rowfactory1(self,cursor,row):
        if(row[4]==row[1]):
            r3="相符"
        elif(row[4]==row[3] and row[3]!='文件无法打开'):
            r3="与备份记录相符"
        else:
            r3=row[4]
        if(row[3]==row[1]):
            r2="相符"
        else:
            r2=row[3]
        if(row[2]==row[1] and row[2]!=None):
            r1="相符"
        else:
            r1=row[2]
        logging.info(f"{row[0]}\n{row[1]}\n{row[2]}\n{row[3]}\n{row[4]}\n")
        return (row[0],row[1],r1,r2,r3)
    def showlist(self,page,table="remoteCheckAll"):
        res=self.curf1.execute(f"select * from '{table}' limit {self.size*page},{self.size}")
        res=res.fetchall()
        if(len(res)==0):
            gridDatas = [(u"无记录", u"无记录", u"无记录", u"无记录",u"无记录")]
        else:
            gridDatas = res
        self.infoTable = MyDataTable(gridDatas, [u"路径", u"校验记录", u"生成校验",u"备份记录",u"备份校验"])
        self.m_grid4.SetTable(self.infoTable, True)
        self.m_grid4.SetColSize(0, 400)
        l=[500,180,120,120,120]
        for  i in range(len(l)):
            self.m_grid4.SetColSize(i, l[i])
        self.m_grid4.SetRowLabelSize(50)
        # self.m_grid4.AutoSize()
        self.m_grid4.Refresh()
    def recoverFilesAll( self, event ):
        msg(self.remotePath)
        event.Skip()
    def copyRemoteDb(self):
        sql=["DROP TABLE IF EXISTS \"remote_hash\";",
             "create table remote_hash (path varchar(256) primary key, hash varchar(128));",
             "DROP TABLE IF EXISTS 'remote_check';",
             "create table remote_check (path varchar(256) primary key, hash varchar(128))",
             "replace into `remote_check`(path) select path from `check`",
             "DROP VIEW IF EXISTS 'remoteCheckAll';",
             '''
             CREATE VIEW "main"."remoteCheckAll" AS 
                SELECT
                remote_hash.path,
                hash.hash AS localhost,
                checks.hash AS "localcheck",
                remote_hash.hash AS remotehash,
                remote_check.hash AS remotecheck
                FROM
                remote_hash
                LEFT JOIN hash ON hash.path = remote_hash.path
                LEFT JOIN remote_check ON remote_check.path = remote_hash.path
                LEFT JOIN checks ON checks.path = remote_hash.path
            ''',
            "DROP VIEW IF EXISTS 'checkListRemote';",
            '''
                CREATE VIEW  'checkListRemote' AS 
                SELECT
                remote_hash.path,
                remote_check.hash
                FROM
                remote_hash
                LEFT JOIN remote_check ON remote_check.path = remote_hash.path;
            ''',
            "DROP VIEW IF EXISTS 'checkListLocal';",
            '''
                CREATE VIEW  'checkListLocal' AS 
                SELECT
                remote_hash.path,
                checks.hash,
                checks.timestamp
                FROM
                remote_hash
                LEFT JOIN checks ON checks.path = remote_hash.path;
            ''',
             ]
        if(not trySql(self.localDb,sql)):
            logging.error("表remote_hash创建失败")
            return False
        remoteDb=sqlite3.connect(self.remotePath+"/"+logfilename+".db", check_same_thread=False)
        rcur=remoteDb.cursor()
        result = rcur.execute("select * from sqlite_master where type = 'table' and name = 'hash'")
        row = result.fetchall()
        if(len(row)<=0):
            self.cur.execute(f"insert into `remote_hash`(path,hash) select path,'无记录' from `check`")
            return False
        self.copyDb_(remoteDb,'check')
        self.copyDb_(remoteDb,'lostfile')
        remoteDb.close()

    def copyDb_(self,remoteDb,table):
        rcur=remoteDb.cursor()
        n=0
        while True:
            sql=f"select path from '{table}' LIMIT {self.size*n},{self.size}"
            res=self.cur.execute(sql)
            res=res.fetchall()
            if(len(res)<=0):break
            for i in res:
                p=i[0].replace("\'","\'\'")
                sql=f"select hash from hash where path = \'{p}\'"
                rres=rcur.execute(sql)
                rres=rres.fetchone()
                rres = f"\'{rres[0]}\'" if rres != None else "'无记录'"
                self.cur.execute(f"REPLACE INTO 'remote_hash' ('path', 'hash') VALUES ('{p}', {rres})")
            n+=1
            remoteDb.commit()
            self.localDb.commit()
        rcur=None
    def recoverFilesSelect( self, event ):
        lt=self.m_grid4.GetSelectionBlockTopLeft()
        rb=self.m_grid4.GetSelectionBlockBottomRight()
        try:
            os.mkdir(f"{self.localPath}\\__breakfile__")
        except BaseException as e:
            logging.info(e)
        n0_=0
        n1=0
        n2=0
        if(len(lt)>0):
            for i in range(lt[0].Row,rb[0].Row+1):
                path_=self.m_grid4.GetCellValue(i,0)
                path_=path_.replace("\\","_")
                path_=path_.lstrip("_")
                lpath=f"{self.localPath}{self.m_grid4.GetCellValue(i,0)}"
                lpath1=f"{self.localPath}\\__breakfile__\\{path_}"
                rpath=f"{self.remotePath}{self.m_grid4.GetCellValue(i,0)}"
                if(os.path.isfile(lpath)):
                    shutil.move(lpath,lpath1)
                else:
                    logging.error(f"文件{lpath}不存在")
                    n1+=1
                if(os.path.isfile(rpath)):
                    shutil.copy2(rpath,lpath)
                    n0_+=1
                else:
                    logging.error(f"文件{lpath}不存在")
                    n2+=1
        else:
            msg("请选择文件")
        msg(f"文件恢复完成{n0_}\n备份失败{n1}\n恢复失败{n2}")
        event.Skip()
    def checkRemoteFile(self, event):
        sql=[
            "DELETE FROM checks WHERE path in (select path from `remote_hash`)",
            "UPDATE 'remote_check' SET 'hash'=NULL"
        ]
        trySql(self.localDb,sql)
        HashThread(db=self.localDb,rootpath=self.localPath,hashTable="checkListLocal",writeTable="checks",expirationDate=-1)
        self.showlist(0)
        event.Skip()

    def uptextCtrl2(self,msg):
        if(msg==u"完成checks"):
            HashThread(db=self.localDb,rootpath=self.remotePath,hashTable="checkListRemote",writeTable="remote_check")
        elif(msg==u"完成remote_check"):
            dlg = wx.MessageDialog(None, "完成校验", "完成校验")
            dlg.ShowModal()
            dlg.Destroy()
            self.showlist(0)
    def back( self, event ):
        sql=[
            "DROP TABLE IF EXISTS 'remote_hash';",
            "DROP TABLE IF EXISTS 'remote_check';",
            "DROP VIEW IF EXISTS 'remoteCheckAll';",
            "DROP VIEW IF EXISTS 'checkListLocal';",
            "DROP VIEW IF EXISTS 'checkListRemote';",
        ]
        trySql(self.localDb,sql)
        self.localDb.commit()

        self.localDb.close()
        self.w2.Destroy()
        self.w2=f2(None,self.localPath)
        p2(self.w2,self.localPath)
        self.w2.Title="查看记录"
        self.w2.Show()
        event.Skip()

if __name__ == "__main__":
    logging.info("run mf2.py")
    app=wx.App()
    f=f2(None,"D:\\markpic")
    p3(f,"D:\\markpic","d:\\hubo1")
    f.Show()
    app.MainLoop()